import pymysql

db = None
cursor = None

def connect_mysql():
    global db
    global cursor
    db = pymysql.connect(
                         host='82.157.127.245',
                         #host='127.0.0.1',
                         user='root',
                         port=3306,
                         password='ydsungan0406',
                         database='weibo',
                         charset='utf8mb4')
    cursor = db.cursor()
def close_mysql():
    if cursor is not None:
        cursor.close()
    if db is not None:
        db.close()

def get_mblog_by_docids(docids):
    #docids是一个docid列表
    connect_mysql()
    res_list = []
    sql = "select * from mblog where id=%s"
    for docid in docids:
        cursor.execute(sql, docid)
        res_list.append(cursor.fetchone())
    close_mysql()
    return res_list

def get_mblog_by_page(size, page):
    connect_mysql()
    sql = "select id, text from mblog limit %s, %s"
    start = (page - 1) * size
    end = page * size
    cursor.execute(sql, (start, end))
    res = cursor.fetchall()
    close_mysql()
    return res

def get_mblog_by_start_id(start_id, size):
    connect_mysql()
    sql = "select id, text from mblog where id >= %s limit %s"
    cursor.execute(sql, (start_id, size))
    res = cursor.fetchall()
    close_mysql()
    return res

def get_count_by_uid(uid):
    query_sql = "select count(id) from uid where id = %s;"
    connect_mysql()
    cursor.execute(query_sql, (uid))
    res = cursor.fetchall()
    close_mysql()
    return res[0]

def insert_uid_by_list(uid_list):
    insert_sql = "insert into uid(id) values (%s);"
    connect_mysql()
    cursor.executemany(insert_sql, (uid_list))
    db.commit()
    close_mysql()

def insert_uid(uid):
    insert_sql = "insert into uid(id) values (%s);"
    connect_mysql()
    cursor.execute(insert_sql, (uid))
    db.commit()
    close_mysql()


def f1():
    #把mblog表里面的所有user_id加入id表
    sql = "select DISTINCT user_id from mblog;"
    connect_mysql()
    cursor.execute(sql)
    res = cursor.fetchall()
    close_mysql()

    for uid in res:
        try:
            insert_uid(uid[0])
        except:
            print("主键重复")

def f2():
    sql = "select mblog_id from mblog GROUP BY mblog_id HAVING count(mblog_id) > 1"
    sql2 = "select id from mblog where mblog_id = %s"
    sql3 = "delete from mblog where id = %s"
    connect_mysql()
    cursor.execute(sql)
    res = cursor.fetchall()
    total = len(res)
    k = 0
    ids = []
    for mblog_id in res:
        cursor.execute(sql2, (mblog_id))
        res = cursor.fetchall()
        res = res[1:]

        for id in res:
            ids.append(id[0])
        if len(ids) >= 10:
            cursor.executemany(sql3, (ids))
            db.commit()
            ids = []
        k += 1
        if k % 100 == 0:
            print("进度:{}%".format(k / total * 100))
    close_mysql()

if __name__ == "__main__":
    #get_mblog_by_page(10, 1)
    #get_count_by_uid("2803301701")
    #f1()
    res = get_mblog_by_docids([1,12])
    print(res)